package Utils;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DbUtil {
    public static Connection getConnection(HttpServletRequest req) throws SQLException {
        HttpSession session = req.getSession();
        String url = (String) session.getAttribute("url");
        String port = (String) session.getAttribute("port");
        String db = (String) session.getAttribute("db");
        String username = (String) session.getAttribute("username");
        String password = (String) session.getAttribute("password");
        String con = "jdbc:mysql://" + url + ":" + port + "/" + db + "?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC";
        return DriverManager.getConnection(con, username, password);
    }

    public static void close(Connection conn, PreparedStatement ps, ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (ps != null) {
                ps.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static int executeUpdate(HttpServletRequest req, HttpServletResponse resp, String sql, Object... params) throws ServletException, IOException {
        int result = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConnection(req);
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1, params[i]);
            }
            result = ps.executeUpdate();
        } catch (SQLException e) {
            req.setAttribute("error", e.getMessage());
            RequestDispatcher rd = req.getRequestDispatcher("error.jsp");
            rd.forward(req, resp);
        } finally {
            close(conn, ps, null);
        }
        return result;
    }

    public static <T> List<T> executeQuery(HttpServletRequest req, String sql, RowMapper<T> rowMapper, Object... params) {
        List<T> list = new ArrayList<T>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection(req);
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1, params[i]);
            }
            rs = ps.executeQuery();
            while (rs.next()) {
                T t = rowMapper.mapRow(rs);
                list.add(t);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(conn, ps, rs);
        }
        return list;
    }

    public static List<String> getColumnCount(HttpServletRequest req, String tableName) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<String> list = new ArrayList<String>();
        try {
            conn = getConnection(req);
            Statement statement = conn.createStatement();
            rs = statement.executeQuery("SELECT * FROM " + tableName + " WHERE 1=0");
            ResultSetMetaData metadata = rs.getMetaData();
            int columnCount = metadata.getColumnCount();
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metadata.getColumnName(i);
                list.add(columnName);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(conn, ps, rs);
        }
        return list;
    }

    public static Map getColumnAndType(HttpServletRequest req, String tableName) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Map map = new HashMap();
        try {
            conn = getConnection(req);
            DatabaseMetaData metaData = conn.getMetaData();
            rs = metaData.getColumns(null, null, tableName, null);
            while (rs.next()) {
                map.put(rs.getString("COLUMN_NAME"), rs.getString("TYPE_NAME"));

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return map;
    }

    public interface RowMapper<T> {
        T mapRow(ResultSet rs) throws SQLException;
    }
}
